package com.studentms.dao.impl;

import com.studentms.bean.Student;
import com.studentms.dao.DBUtils;
import com.studentms.dao.StudentDao;
import com.studentms.util.StudentEnum;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDaoImpl extends DBUtils implements StudentDao {
    //获取学生信息
    @Override
    public List<Student> getStudents(String name,String stuno,int sex,int pageIndex,int pageSize) {
        List list = new ArrayList<Student>();
        List params = new ArrayList();//参数集合
        try {
            //where 1=1是一个恒成立的,在没有任何条件的限定下,全部查询
            StringBuffer sqlbuf = new StringBuffer(" select * from student where 1=1 and state!=4 ");//查询所有学生信息
            //sql语句拼接
            if (name!=null&&name.length()>0){
                //名称查询,使用模糊查询,需要使用%进行拼接
                sqlbuf.append(" and stuname like ? ");//需要注意的是字符串拼接时前后有字符串
                params.add("%"+name+"%");//绑定数据stuname
            }
            if (stuno!=null&&stuno.length()>0){
                sqlbuf.append(" and stuno=? ");
                params.add(stuno);//绑定数据stuno
            }
            if (sex!=-1){
                sqlbuf.append(" and sex=? ");
                params.add(sex);//绑定数据sex
            }

            //分页
            sqlbuf.append(" limit ?,?"); //开始位置,结束位置 1 5  limit 0,5
            //limit 分页公式 开始位置,结束位置 (pageIndex-1)*pageSize,pageSize
            params.add((pageIndex-1)*pageSize);
            params.add(pageSize);

            resultSet = query(sqlbuf.toString(),params);
            while(resultSet.next()){//判断是否有下一个
                Student student = new Student();
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setStuName(resultSet.getString("stuname"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setEmail(resultSet.getString("email"));
                student.setRegistered(resultSet.getString("registered"));
                student.setAddress(resultSet.getString("address"));
                student.setProfession(resultSet.getString("profession"));
                student.setIdNumber(resultSet.getString("idNumber"));
                student.setPolitics(resultSet.getString("politics"));
                student.setRegDate(resultSet.getDate("regDate"));
                student.setState(resultSet.getInt("state"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setGid(resultSet.getInt("gid"));

                list.add(student);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return list;//返回list集合
    }

    @Override//统计总条数
    public int total(String name, String stuno, int sex) {
        int total=0;
        try {
            List params = new ArrayList();//参数集合
            //where 1=1是一个恒成立的,在没有任何条件的限定下,全部查询
            StringBuffer sqlbuf = new StringBuffer(" select count(*) from student where 1=1 and state!=4 ");//查询所有学生信息
            //sql语句拼接
            if (name!=null&&name.length()>0){
                //名称查询,使用模糊查询,需要使用%进行拼接
                sqlbuf.append(" and stuname like ? ");//需要注意的是字符串拼接时前后有字符串
                params.add("%"+name+"%");//绑定数据stuname
            }
            if (stuno!=null&&stuno.length()>0){
                sqlbuf.append(" and stuno=? ");
                params.add(stuno);//绑定数据stuno
            }
            if (sex!=-1){
                sqlbuf.append(" and sex=? ");
                params.add(sex);//绑定数据sex
            }
            resultSet = query(sqlbuf.toString(),params);
            while(resultSet.next()){
                total = resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }

        return total;
    }

    //添加学生信息
    @Override
    public int insertStudent(Student student) {
        int i=0;
        try {
            //student表中插入数据
            String sql = "insert into student values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            List params = new ArrayList();
            //绑定参数,顺序要保持一致
            params.add(student.getStuName());
            params.add(student.getStuNo());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add(student.getEmail());

            params.add(student.getAddress());
            params.add(student.getProfession());
            params.add(student.getIdNumber());
            params.add(student.getPolitics());
            params.add(new Date());//获取的是本地时间

            params.add(StudentEnum.READING.type);//1表示在读
            // 2休学   3退学     4删除
            params.add(student.getIntroduction());
            params.add(student.getGid());
            i = update(sql,params); //更新返回i
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return i;
    }

    @Override //找到主键id
    public Student findById(int sid) {
        Student student = new Student();
        try {
            String sql = "select * from student where stuid=?";
            List params = new ArrayList();
            params.add(sid);

            resultSet = query(sql,params);
            while(resultSet.next()){
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setStuName(resultSet.getString("stuname"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setEmail(resultSet.getString("email"));
                student.setRegistered(resultSet.getString("registered"));
                student.setAddress(resultSet.getString("address"));
                student.setProfession(resultSet.getString("profession"));
                student.setIdNumber(resultSet.getString("idNumber"));
                student.setPolitics(resultSet.getString("politics"));
                student.setRegDate(resultSet.getDate("regDate"));
                student.setState(resultSet.getInt("state"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setGid(resultSet.getInt("gid"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return student;
    }

    @Override//更新(修改)学生信息
    public int updateStudent(Student student) {
        int update=0;
        try {
            //完善非主键列以外的列的更新
            //注意要补全,
//            String sql = "update student set stuname=?,address=?,sex=? where stuid=?";
            String sql = "update student set stuname=?,stuno=?,sex=?,phone=?,email=?,registered=?,address=?,profession=?,idnumber=?,politics=?,introduction=?,gid=? where stuid=?";
            List params = new ArrayList();
            params.add(student.getStuName());
            params.add(student.getStuNo());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add(student.getEmail());
//
            params.add(student.getRegistered());
            params.add(student.getAddress());
            params.add(student.getProfession());
            params.add(student.getIdNumber());
            params.add(student.getPolitics());
            params.add(student.getIntroduction());
            params.add(student.getGid());
            params.add(student.getStuId());

            update = update(sql,params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    @Override   //删除学生信息
    public int deleteStudent(String sid) {
        int update = 0;
        try {
            String sql = "update student set state=? where stuid=?";
            List params = new ArrayList();
            params.add(StudentEnum.DELETE.type);//假删除
            params.add(sid);
            update = update(sql,params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return update;
    }
}
